let express = require("express")(); 
let expressStatic = require("express-static"); 
const port = 80;
let mysql = require("mysql");
let sql = mysql.createConnection({
	host     : 'localhost',
	user     : 'root',
	password : '123456',
	database : 'school',
	timezone : "08:00"
});
sql.connect();

express.get("/login",function(request,response){
	sql.query(`SELECT * FROM user WHERE username="${request.query.username}" AND password="${request.query.password}"`,(error,data)=>{
		if(error){
			response.send("error");
			console.log(error);
		}else{
			if(data.length === 0){
				response.send("error");
			}else{
				response.send("success");
			}
			
		}
	})
})
express.get("/register",function(request,response){
	sql.query(`INSERT INTO user (username,password) VALUES ("${request.query.username}","${request.query.password}")`,(error,data)=>{
		if(error){
			response.send("error");
			console.log(error);
		}else{
			response.send("success");
		}
	})
})
express.get("/getData",function(request,response){
	sql.query(`SELECT * FROM students`,function(error,data){
		if(error){
			response.send("error");
			console.log(error);
		}else{
			response.send(data);
		}
	})
})
express.get("/delete",function(request,response){
	sql.query(`DELETE FROM students WHERE id=${request.query.id}`,function(error,data){
		if(error){
			response.send("error");
			console.log(error);
		}else{
			response.send("success");
		}
	})
})
express.get("/add",function(request,response){
	sql.query(`INSERT INTO students (name,age,sex,joinDate,province,city,area) VALUES ("${request.query.name}",${request.query.age},"${request.query.sex}","${request.query.joinDate}","${request.query.province}","${request.query.city}","${request.query.area}")`,function(error,data){
		if(error){
			response.send("error");
			console.log(error);
		}else{
			response.send("success");
		}
	})
})

express.get("/getcity",function(request,response){
	sql.query(`SELECT * FROM students WHERE id=${request.query.id}`,function(error,data){
		if(error){
			response.send("error");
			console.log(error);
		}else{
			response.send(data[0]);
		}
	})
})
express.get("/modify",function(request,response){
	sql.query(`Update students  Set name="${request.query.name}",age="${request.query.age}",sex="${request.query.sex}",province="${request.query.province}",city="${request.query.city}",area="${request.query.area}",joinDate="${request.query.joinDate}" Where id=${request.query.id}`,function(error,data){
		if(error){
			response.send("error");
			console.log(error);
		}else{
			response.send("success");
		}
	})
})
express.use(expressStatic(__dirname + '/static'));
console.log(`running at ${port}`)
express.listen(port)